<?php
/*
    The database class wraps PHP Data Objects (PDO) with optimization and convenience methods.
    
    It stores open database connections internally, to avoid opening new connections unnecessarily.
    It exposes Database->db, the PDO object for the currently active database, for direct use. It
    also wraps many of PDO's methods for error handling (eg, when Database::ERROR_PRINT set). When
    an error occurs, it ignores all further calls until the next Connect() or resetException() call.

    NOTE: due to an inherit limitation of PDO, the database specified in Connect() is not
    necessarily set as the default database for queries. Always specify the database in 
    queries, like "SELECT foo FROM {$wiki}.table".
    
    ----
    
    The toolserver subclass adds optimizations for the Wikimedia Toolserver. On construction, the
    class fetches wiki and database data from the toolserver DB. When connecting to a database name,
    it aliases it to its server host to minimize the number of connections to the number of servers.
*/

/**
 * Provides database operations with optimizations and connection caching.
 */
class Database {
    #################################################
    ## Properties
    #################################################
    /* constants */
    const ERROR_THROW = 1;
    const ERROR_PRINT = 2;
    const ERROR_SUPPRESS = 4;

    const DRIVER = 'mysql';
    const ERROR_MODES = 7;

    /* default configuration */
    protected $error_mode = 2; // ERROR_THROW | ERROR_PRINT
    protected $config_file; // set in constructor
            
    /* default settings */
    protected $default_user;
    protected $default_password;
    
    /* connection arrays */
    protected $connections = Array(); // hash of host => PDO
    protected $exceptions = Array();  // hash of host => Exception
        
    /* current/previous connection's details */
    protected $host;
    protected $username;
    protected $password;
    protected $database;
    protected $borked = false; // boolean whether error occured
    
    protected $prev_host;
    protected $prev_username;
    protected $prev_password;
    protected $prev_database;
    
    /* last query performed, for convenience methods */
    protected $query = NULL;
    
    /* public interface */
    public $db = NULL;
    
    /* internal */
    private $disposed = false;
    

    #################################################
    ## Public DB methods
    #################################################
    #############################
    ## Constructor
    #############################
    /**
     * Initialize a database object.
     *
     * @param string $default_username The username to use when authenticating to the database, or NULL to retrieve it from the user configuration file.
     * @param string $default_password The password to use when authenticating to the database, or NULL to retrieve it from the user configuration file.
     * @param integer $options Additional mode options which can be bitwise ORed together (available options: ERROR_THROW, ERROR_PRINT, ERROR_SUPPRESS).
     */
    public function __construct( $default_username = NULL, $default_password = NULL, $options = NULL ) {
        /* configuration */
        $this->config_file = "/home/" . get_current_user() . "/.my.cnf";
    
        /* get default login details */
        $this->default_username = $default_username;
        $this->default_password = $default_password;

        if( (!$default_username || !$default_password) && file_exists($this->config_file) ) {
            $config = parse_ini_file( $this->config_file );
            $this->default_username = ( $default_username ? $default_username : $config['user'] );
            $this->default_password = ( $default_password ? $default_password : $config['password'] );
        }
        
        /* get options */
        if( $options & self::ERROR_MODES )
            $this->error_mode = self::ERROR_MODES & $options;
    }
    
    
    #############################
    ## Destructor
    #############################
    /**
     * Close all database connections and release resources.
     */
    public function Dispose() {
        if( $this->disposed )
            return;
        
        $keys = array_keys( $this->connections );
        foreach( $keys as $key )
            $this->connections[$key] = NULL;
        
        $this->disposed = true;
    }
    
    /**
     * Close all database connections and release resources.
     */
    public function __destruct() {
        $this->Dispose();
    }
    
    
    #############################
    ## Connect
    #############################
    /**
     * Open a database connection. This will reuse an existing server connection if it has been previously opened; make sure to explicitly specify the database in queries (eg, "SELECT * FROM dbname.table").
     * @param $host The server address to connect to.
     * @param $database The name of the database to connect to.
     * @param $username The username to use when authenticating to the database, or NULL to authenticate with the default username.
     * @param $password The password to use when authenticating to the database, or NULL to authenticate with the default password.
     * @return bool Whether the connection was successfully established.
     */
    public function Connect( $host, $database = NULL, $username = NULL, $password = NULL ) {
        /* change states */
        $this->borked = false;
        
        $this->prev_host     = $this->host;
        $this->prev_username = $this->username;
        $this->prev_password = $this->password;
        $this->prev_database = $this->database;
        
        $this->host = $host;
        $this->username = ( $username ? $username : $this->default_username );
        $this->password = ( $password ? $password : $this->default_password );
        $this->database = $database;
        
        /* create new connection, if can't recycle one */
        if( !isset($this->connections[$host]) || !$this->connections[$this->host] ) {
            try {
                $this->connections[$this->host] = new PDO(
                    self::DRIVER . ":host={$this->host};dbname={$database}",
                    $this->username,
                    $this->password
                );
                $this->connections[$this->host]->setAttribute(
                    PDO::ATTR_ERRMODE,
                    PDO::ERRMODE_EXCEPTION
                );
            }
            catch( PDOException $exc ) {
                return $this->handleException( $exc, "Could not connect to database host '{$host}'" );
            }
        }
        
        /* alias connection */
        $this->db = &$this->connections[$this->host];
        return true;
    }

    /**
     * Reopen the previous connection. This is typically used after establishing a temporary connection to a different database.
     * @return bool Whether the connection was successfully established.
     */
    public function ConnectPrevious() {
        return $this->Connect( $this->prev_host, $this->prev_database, $this->prev_username, $this->prev_password );
    }


    #############################
    ## Query
    #############################
    /**
     * Submit a parameterized SQL query to the database.
     * @param $sql string The raw SQL command to submit.
     * @values $values array The values to substituted for parameterized placeholders in the SQL command.
     * @return PDOStatement The underlying query object, for specialized cases. This return value should generally be ignored.
     */
    public function Query( $sql, $values = Array() ) {
        if( $this->borked )
            return NULL;
        try {
            $this->query = NULL;
            $this->query = $this->db->prepare( $sql );
            $this->query->execute( $values );
            return $this->query;
        }
        catch( Exception $exc ) {
            return $this->handleException( $exc, "Could not perform query:<br /><small>" . $this->getQueryDebugData() . "</small>" );
        }
    }
    

    #############################
    ## Read row from query result
    #############################
    /**
     * Fetch the next row as a hash of field names and values, and advance the internal pointer.
     * @return array The hash of field names and values.
     */
    public function fetchAssoc() {
        if( $this->borked )
            return NULL;
        try {
            return $this->query->fetch( PDO::FETCH_ASSOC );
        }
        catch( PDOException $exc ) {
            return $this->handleException( $exc, "Could not fetch result from query:<br /><small>" . $this->getQueryDebugData() . "</small>" );
        }
    }

    /**
     * Fetch a single value from the next row, and advance the internal pointer.
     * @param $columnNumber int The numeric index of the column to retrieve.
     * @return string The value of the retrieved field.
     */
    public function fetchColumn( $columnNumber = 0 ) {
        return $this->fetchValue( $columnNumber );
    }
    public function fetchValue( $columnNumber = 0 ) {
        if( $this->borked )
            return NULL;
        try {
            return $this->query->fetchColumn( $columnNumber );
        }
        catch( PDOException $exc ) {
            return $this->handleException( $exc, "Could not fetch result from query:<br /><small>" . $this->getQueryDebugData() . "</small>" );
        }
    }
    
    /**
     * Fetch all result rows as an array of field name & value hashes.
     * @return array An array of field name & value hashes.
     */
    public function fetchAllAssoc() {
        if( $this->borked )
            return NULL;
        try {
            return $this->query->fetchAll( PDO::FETCH_ASSOC );
        }
        catch( PDOException $exc ) {
            return $this->handleException( $exc, "Could not fetch result from query:<br /><small>" . $this->getQueryDebugData() . "</small>" );
        }
    }

    
    /**
     * Get the number of rows returned by the query.
     * @return int The number of rows returned by the query.
     */
    public function countRows() {
        if( !$this->query )
            return 0;
        return $this->query->rowCount();
    }


    #################################################
    ## Internal methods
    #################################################
    #############################
    ## Handle a PDO exception
    #############################
    protected function handleException( $exc, $error_message = NULL ) {
        $this->exceptions[$this->host] = $exc;
        $this->borked = true;
        
        if( $this->error_mode & self::ERROR_PRINT )
            $this->printException( $error_message );
        if( $this->error_mode & self::ERROR_THROW )
            throw $exc;
        return false;
    }
    

    #############################
    ## Print exception details
    #############################
    protected function printException( $error_message = NULL ) {
        if( $error_message )
            echo "<div class='error'>{$error_message}<br /><small>Exception: ", $this->exceptions[$this->host]->getMessage(), "</small></div>";
        else
            echo "<div class='error'>Exception: ", $this->exceptions[$this->host]->getMessage(), "</div>";
        return false;
    }


    #############################
    ## Get query debug data
    #############################
    protected function getQueryDebugData() {
        ob_start();
        $this->query->debugDumpParams();
        return ob_get_clean();
    }
}

class Toolserver extends Database {
    #################################################
    ## Properties
    #################################################
    protected $dbn_hosts = Array();   // hash of dbname => host
    protected $dbn_domains = Array(); // hash of dbname => domain
    protected $dbn_locked = Array();  // hash of dbname => wiki is locked

    #############################
    ## Constructor
    #############################
    public function __construct( $default_username = NULL, $default_password = NULL, $options = NULL ) {
        parent::__construct( $default_username, $default_password, $options );
        
        /* fetch toolserver data */
        $this->fetchOptimization();
    }
    
    #############################
    ## Connect to host or DBN
    #############################
    public function Connect( $host, $database = NULL, $username = NULL, $password = NULL ) {
        /* alias host */
        if( isset($this->dbn_hosts[$host]) && $this->dbn_hosts[$host] ) {
            $database = $host;
            $host     = $this->dbn_hosts[$host];
        }
        
        parent::Connect( $host, $database, $username, $password );
    }
    
    #############################
    ## Normalize db_name
    #############################
    public function normalizeDbn( $dbn ) {
        $dbn = str_replace( '-', '_', $dbn );
        if( substr($dbn, -2) != '_p' )
            $dbn .= '_p';
        return $dbn;
    }

    #################################################
    ## Public getters
    #################################################
    #############################
    ## Get DBN => DB host hash
    #############################
    public function getDbnHosts() {
        return $this->dbn_hosts;
    }


    #############################
    ## Get DBN => domain hash
    #############################
    public function getWikis() {
        return $this->dbn_domains;
    }
    
    
    ############################
    ## Get host for a DBN
    ############################
    public function getHost( $dbn ) {
        if( isset($this->dbn_hosts[$dbn]) )
            return $this->dbn_hosts[$dbn];
        return NULL;
    }
    
    
    ############################
    ## Get boolean indicating wiki locked?
    ############################
    public function getLocked( $dbn ) {
        if( isset($this->dbn_locked[$dbn]) )
            return $this->dbn_locked[$dbn];
        return NULL;
    }


    #################################################
    ## Public query methods
    #################################################
    #############################
    ## Get a global account's home wiki
    #############################
    public function getHomeWiki( $user ) {
        try {
            $this->Connect( 'metawiki_p' );

            $query = $this->db->prepare( 'SELECT lu_wiki FROM centralauth_p.localuser WHERE lu_name=? AND lu_attached_method IN ("primary", "new") LIMIT 1' );
            $query->execute(Array( $user ));
            
            $this->ConnectPrevious();
            
            $wiki = $query->fetchColumn();
            if( $wiki )
                return $wiki . '_p';
            return NULL;
        }
        catch( PDOException $exc ) {
            return $this->handleException( $exc, "Could not retrieve home wiki for user '{$user}'." );
        }
    }
    
    
    #############################
    ## Get a global account's unified wikis
    #############################
    public function getUnifiedWikis( $user ) {
        try {
            $this->Connect( 'metawiki_p' );
            
            $query = $this->db->prepare( 'SELECT lu_wiki FROM centralauth_p.localuser WHERE lu_name=?' );
            $query->execute(Array( $user ));
            
            $this->ConnectPrevious();
            
            $wikis = Array();
            foreach( $query as $row )
                $wikis[] = $row['lu_wiki'] . '_p';
            
            return $wikis;
        }
        catch( PDOException $exc ) {
            return $this->handleException( $exc, "Could not retrieve unified wikis for user '{$user}'." );
        }
    }


    #############################
    ## Get a local account's details (id, registration date, edit count)
    #############################
    public function getUserDetails( $wiki, $user_name, $date_format = '%Y-%b-%d %H:%i' ) {
        try {
            /* fetch availabe user details */
            $query = $this->db->prepare( "SELECT user_id AS id, user_registration AS registration_raw, DATE_FORMAT(user_registration, '{$date_format}') as registration, user_editcount AS editcount FROM {$wiki}.user WHERE user_name = ? LIMIT 1" );
            $query->execute(Array( $user_name ));
            $user = $query->fetch( PDO::FETCH_ASSOC );
            
            /* if needed, use more complex date algorithm */
            if( $user['id'] && !$user['registration_raw'] ) {
                $date = $this->getRegistrationDate( $wiki, $user['id'], $date_format, true );
                $user['registration_raw'] = $date['raw'];
                $user['registration']     = $date['formatted'];
            }
            
            /* done! */
            return $user;
        }
        catch( PDOException $exc ) {
            return $this->handleException( $exc, "Could not retrieve local account details for user '{$user_name}' at wiki '{$wiki}'." );
        }
    }


    #############################
    ## Get a local account's registration date as a (raw,formatted) array
    #############################
    public function getRegistrationDate( $wiki, $user_id, $format = '%Y-%m-%dT%H:%i:%s', $skip_user_table = false ) {
        if( $this->borked )
            return NULL;
        try {
            /* try date field in user table */
            if( !$skip_user_table ) {
                $query = $this->db->prepare( "SELECT user_registration AS raw, DATE_FORMAT(user_registration, '{$format}') AS formatted from {$wiki}.user WHERE user_id=? LIMIT 1" );
                $query->execute(Array( $user_id ));
                $date = $query->fetch( PDO::FETCH_ASSOC );
                if( isset($date['raw']) )
                    return $date;
            }
            
            /* try extracting from logs */
            $query = NULL;
            $query = $this->db->prepare( "SELECT log_timestamp AS raw, DATE_FORMAT(log_timestamp, '{$format}') AS formatted FROM {$wiki}.logging WHERE log_user = ? AND log_type = 'newusers' AND log_title = 'Userlogin' LIMIT 1" );
            $query->execute(Array( $user_id ));
            $date = $query->fetch( PDO::FETCH_ASSOC );
            if( isset($date['raw']) )
                return $date;
            
            /* failed */
            return Array( 'raw' => NULL, 'formatted' => 'in 2005 or earlier' );
        }
        catch( PDOException $exc ) {
            return $this->handleException( $exc, "Could not retrieve registration date for user id '{$user_id}'." );
        }
    }
    
    
    #################################################
    ## Internal methods
    #################################################
    #############################
    ## Fetch toolserver DB data for optimizations
    #############################
    protected function fetchOptimization() {
        try {
            /* connect to toolserver DB host */
            $this->Connect( 'metawiki-p.db.toolserver.org', 'toolserver' );
            
            /* fetch dbn data */
            $query = $this->db->Query( "SELECT dbname, domain, server, is_closed FROM toolserver.wiki WHERE dbname NOT IN('closed_zh_twwiki_p', 'sep11wiki_p') ORDER BY dbname" );
            
            /* store data */
            foreach( $query as $row ) {
                $this->dbn_hosts[$row['dbname']]   = "sql-s{$row['server']}.toolserver.org";
                $this->dbn_domains[$row['dbname']] = $row['domain'];
                $this->dbn_locked[$row['dbname']]  = (bool)$row['is_closed'];
            }
        
            /* store connection */
            $this->conns[$this->dbn_hosts['metawiki_p']] = $this->connections['metawiki-p.db.toolserver.org'];
        }
        catch( PDOException $exc ) {
            return $this->handleException( $exc, "Could not retrieve wiki and DB data in Toolserver constructor." );
        }
    }
}